select
CONCAT( "", demographic_no, "" ) AS "DemoLink",
CONCAT( "E" ) AS "eChart" ,
first_name, last_name, round(datediff(now(), concat(year_of_birth, '-', month_of_birth, '-', date_of_birth))/365,0) as "Age", provider_no,
(select DATE_FORMAT(prevention_date, '%Y-%m-%d') from preventions where prevention_type in ('{prevention_name}') and demographic_no=demographic.demographic_no and deleted=0 order by prevention_date desc limit 1) as "Last date",
(select next_date from preventions where prevention_type in ('{prevention_name}') and demographic_no=demographic.demographic_no and deleted=0 order by prevention_date desc limit 1) as "Next date",
CONCAT( "Prevention" ) AS "PreventionsLink"
from demographic
where patient_status='AC'
and ('{gender}' = '' OR '{gender}' = sex)
and provider_no={provider}
and datediff(now(), concat(year_of_birth, '-', month_of_birth, '-', date_of_birth))/365 >= {min_age}
and datediff(now(), concat(year_of_birth, '-', month_of_birth, '-', date_of_birth))/365 < {max_age}
and not exists (select * from preventions where prevention_type in ('{prevention_name}') and demographic_no=demographic.demographic_no and refused>0)
and
#(
#demographic_no not in (
#select p.demographic_no from preventions p where p.prevention_type in ('{prevention_name}') and p.prevention_date >= DATE_SUB(NOW(), INTERVAL {years} YEAR))
#)
(
(not exists
(select p.id from preventions p where p.prevention_type in ('{prevention_name}') and p.prevention_date >= DATE_SUB(NOW(), INTERVAL {years} YEAR)
and deleted=0 and refused=0 and p.demographic_no=demographic.demographic_no)
)
OR
(
(select IF(DATE(p1.next_date)>DATE("1990-01-01"),(NOW()>DATE(p1.next_date)), FALSE) from preventions p1 where p1.prevention_type in ('{prevention_name}') and p1.demographic_no=demographic.demographic_no and p1.deleted=0
order by p1.prevention_date desc limit 1)
)
)
order by last_name asc
SELECT provider_no,
CONCAT(first_name, ' ',last_name) AS NUM
FROM provider
WHERE provider.ohip_no <> ''
ORDER BY last_name;
All
Female
Male